Server-side enforcement of complex, interrelated rules, part 1

By David Wendelken

David Wendelken is a senior consultant for Tactics, Inc. and uses examples like this for creating training materials. He is the co-author of The Oracle Designer/2000 Handbook. You can reach David by phone at (404) 248-1226 or via E-mail at 73523.2344@compuserve.com.

Imagine that you want to partition the integrated system you’re building into a set of applications that can be installed in a modular, mix-and-match manner. For example, suppose you’re designing a multi-company accounting system for resale, in which some customers use all of the separate applications, while others want just Payroll or just Job Cost. Let’s explore how we might do that!

The Payroll application obviously requires an employee table. So does the Job Cost application. However, the business rules we must enforce for a Job Cost setup (without Payroll) are less stringent than the rules when Payroll is installed. This causes an awkward problem: Do we subject our Job Cost (only) customers to the inconveniences of the far stricter Payroll rules? If not, how do we design our system while satisfying the eight goals in Table A?

Table A: These are the goals for our integrated system.

1. Have the database hide the Payroll-specific columns from Job Cost programs, and vice versa.
2. Require the database to enforce different rules on new record inserts, based upon whether Payroll and/or Job Cost are installed.
3. Enforce different rules on updates of existing records, based upon whether Payroll and/or Job Cost are installed.
4. Allow incremental updates to improve employee data for existing records. Have the database apply the appropriate rules when a customer uses Job Cost for a while, then adds Payroll. For example, existing employee records lack the mandatory Payroll-specific detail. (We also need to account for using Payroll first, then installing Job Cost.) Let’s be kind to the Payroll clerk, who might have to gather information quickly (and need to enter it right away) and might not have all the required data. We’ll allow incremental changes to be made to the employee data, provided there’s no inadvertent attempt to make the existing validation problems for the record worse than they already are.
5. Have the database prepare for the situation where a customer installs Job Cost and Payroll, then de-installs Payroll but continues to use Job Cost, or vice versa. We need to decide, on a column-by-column basis, whether to forbid change or set the columns to null.
6. Design the database to keep the customers from entering Payroll transactions while the data about their employees is incomplete. We’ll have the database prevent people from entering data into any Payroll transaction table until all the required employee columns have been filled in. Naturally, we’ll need to allow data to be entered into the employee table, as well as in various reference code tables.
7. Of course, our clients will want to implement security on a per user/per company basis. In other words, a user who is authorized to alter Payroll data in company 1 may not be authorized to do so in company 2. We want the server-side code to enforce security, not the individual Job Cost and Payroll programs.
8. While we’re at it (just to add a bit of challenge to this task), let’s make it easy for our programmers to generate forms and reports with the Designer/2000 Code Generators. After all, that’s certainly an important factor in any solution for developers who use Designer/2000.

In this first article in our series on using server-side enforcement, we’ll show how to accomplish the first four goals. Next month, we’ll take care of the next two goals. We’ll take care of the remaining goals in future issues.

First, let’s look at the relevant tables. Since our task here is to show how to accomplish these goals, not to build a full-fledged accounting system, our data structures will contain the minimum necessary to demonstrate the concept. Listing A shows the employee table our sample application will be based upon.

Listing A: Note that the module would not use this table directly.

CREATE TABLE employee
(company_id     NUMBER(3,0)  NOT NULL
,employee_id    VARCHAR2(9)  NOT NULL
,name           VARCHAR2(30) NOT NULL
,annual_salary  NUMBER(9,2)
,citizenship    VARCHAR2(2));

ALTER TABLE employee
ADD (CONSTRAINT emp_pk PRIMARY KEY (company_id,employee_id));

Table B contains the rules for the columns in the employee table, where the rules differ based upon whether Job Cost or Payroll is installed.

Table B: These column-validation rules for the EMPLOYEE table differ based on whether Job Cost or Payroll is installed.

We won’t pretend that there’s any valid business rule behind treating annual_salary and citizenship differently when updating an employee record if Job Cost is installed but Payroll is not. However, the difference does illustrate what you can accomplish.

Now that we know the rules, let’s start attaining our eight goals.

Goal 1: Have the database hide the Payroll-specific columns from Job Cost programs, and vice versa.

This is an easy one. As shown in Listing B , we’ll create two views, one for Job Cost programs to use and one for Payroll programs. Why not just create a Job Cost view and let the Payroll programs use the underlying table directly? Because with the next release of our Job Cost system, we’re bound to discover a column that Job Cost users would want, but Payroll (only) users wouldn’t!

Listing B: These views hide unwanted columns.

/* A view used by Job Cost modules.*/
CREATE VIEW jc_employee AS 
SELECT company_id
      ,employee_id
      ,name
  FROM employee WITH CHECK OPTION;

/* A view used by Payroll modules. */
CREATE VIEW pr_employee AS 
SELECT company_id
      ,employee_id
      ,name
      ,annual_salary
      ,citizenship
  FROM employee WITH CHECK OPTION;

Goal 2: Require the database to enforce different rules on new records inserts.

To implement this rule, our code will have to know whether Payroll and/or Job Cost are installed. Therefore, a table holding that information would be appropriate. Now that we think about it a bit more, our users might want Payroll installed for one company in their database but not for another company in the same database. Listing C describes a sample table to hold the installation status.

Listing C: This is the master control table with one record per company.

*/
CREATE TABLE company_control
(company_id     NUMBER(3,0)  NOT NULL
,name           VARCHAR2(30) NOT NULL
,Payroll_installed_ind 
                CHAR(1)      NOT NULL 
 CONSTRAINT comc_cc_pii CHECK
 (Payroll_installed_ind IN ('Y','N'))
,jobcost_installed_ind  
                CHAR(1)      NOT NULL 
 CONSTRAINT comc_cc_jii CHECK 
 (jobcost_installed_ind IN ('Y','N'))
,invalid_employees_ind  
                CHAR(1)      NOT NULL 
 CONSTRAINT comc_cc_iei CHECK
 (invalid_employees_ind IN ('Y','N')));

ALTER TABLE company_control
ADD (CONSTRAINT comc_pk PRIMARY KEY (company_id));

/* Let's be tidy and set up foreign keys. 
*/
ALTER TABLE employee
ADD (CONSTRAINT emp_fk_comc 
FOREIGN KEY (company_id) REFERENCES company_control (company_id));

Payroll_installed_ind (indicator) and jobcost_installed_ind record whether, for a given company, Payroll or Job Cost is installed. The application installation programs set these two indicators correctly when their respective subsystems are installed or de-installed. The invalid_employees_ind is set when we install Payroll and find out that pre-existing employee records (created when only Job Cost was installed) lack essential Payroll information.

Now that we have a place to indicate whether Payroll or Job Cost is installed, we need to use that information. Listing D contains a brace of database functions that enable us to find out whether Payroll or Job Cost is installed. They are in a package called system_control. This package will grow as we implement additional goals with our server-side code.

Listing D: These database functions check the installation status.

CREATE OR REPLACE PACKAGE system_control IS
- This is a package to demonstrate a
- number of techniques. It should
- not be used in production without
- more robust error tracking.

/* Returns (Y)es, (N)o, or (E)rror */
FUNCTION  is_Payroll_installed (p_company_id IN NUMBER) RETURN CHAR;

/* Returns (Y)es, (N)o, or (E)rror */
FUNCTION is_jobcost_installed 
(p_company_id IN NUMBER) RETURN CHAR;
END;
/

CREATE OR REPLACE PACKAGE BODY system_control IS

FUNCTION is_payroll_installed (p_company_id IN NUMBER) RETURN CHAR IS
 ws_pr_installed_ind   company_control.payroll_installed_ind%TYPE;
BEGIN
  SELECT payroll_installed_ind 
    INTO ws_pr_installed_ind
    FROM company_control      
   WHERE company_id = p_company_id;
  RETURN ws_pr_installed_ind;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'E';
END;

FUNCTION is_jobcost_installed (p_company_id IN NUMBER) RETURN CHAR IS
 - same idea as is_payroll_installed
 ..
 .. 
END;

We now need a database trigger on the employee table to require the database to enforce different rules on new record inserts. Listing E contains a database trigger to implement this goal. We must add that the code is optimized for clarity, not efficiency, and that error-handling has been minimized (for space reasons) below the bounds of user acceptability.

Listing E: This trigger enforces goals on inserts of new employee records.

CREATE OR REPLACE TRIGGER emp_bir 
  BEFORE INSERT ON employee 
  FOR EACH ROW
DECLARE
  install_status_unknown EXCEPTION;
  need_data              EXCEPTION;
  unwanted_data          EXCEPTION;
  ws_is_pr_installed company_control.payroll_installed_ind%TYPE
     := system_control.is_payroll_installed(:new.company_id);
  ws_is_jc_installed company_control.jobcost_installed_ind%TYPE
     := system_control.is_jobcost_installed(:new.company_id);
BEGIN
  IF ws_is_pr_installed = 'E' 
  OR ws_is_jc_installed = 'E' THEN
     RAISE install_status_unknown;
  END IF; 
  - There are four remaining combinations.
  IF  ws_is_pr_installed = 'Y'
  AND ws_is_jc_installed = 'Y' THEN
      - Citizenship is mandatory.
      IF :new.citizenship IS NULL
      THEN
         RAISE need_data;
      END IF;
  END IF;
  IF  ws_is_pr_installed = 'Y'
  AND ws_is_jc_installed = 'N' THEN
      - Citizenship is mandatory.
      IF :new.citizenship IS NULL
      THEN
         RAISE need_data;
      END IF;
  END IF;
  IF  ws_is_pr_installed = 'N'
  AND ws_is_jc_installed = 'Y' THEN
      - We don't want citizenship or annual_salary data.
      IF :new.annual_salary IS NOT NULL 
      OR :new.citizenship IS NOT NULL       THEN
         RAISE unwanted_data;
      END IF;
  END IF;
  IF  ws_is_pr_installed = 'N'
  AND ws_is_jc_installed = 'N' THEN
      - We don't record employees at all.
      RAISE unwanted_data;
  END IF;
EXCEPTION
  WHEN install_status_unknown THEN
    RAISE_APPLICATION_ERROR(-20001,'Install status unknown.');
  WHEN unwanted_data THEN
    RAISE_APPLICATION_ERROR(-20001,'Unwanted data.');
  WHEN need_data THEN
    RAISE_APPLICATION_ERROR(-20001,'Need more data.');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE ││ ':' ││ SQLERRM); 
END;
/

Goal 3: Enforce different rules on updates of existing records, based upon whether Payroll and/or Job Cost are installed.

Goal 4: Allow incremental updates to improve employee data for existing records.

Goal 3 is similar to the problem we faced when inserting new records. Goal 4 complicates this programming task, but doesn’t conceptually alter what needs to be done. Let’s recap the logic of the situation we could be placed in when the installation status of Payroll or Job Cost changes:

• Already existing (thus previously valid) employee records may become invalid.
• New records always follow the current rules, or they aren’t allowed into the database. This important point means that the number of employee records in violation of the current rules can’t increase.
• Therefore, existing employee records are in a completely valid state or a partially invalid one. The term "partially invalid" means that some of the columns in the record may in fact be valid.
• If a record is completely valid, the current update rules fully apply.
• If some columns are valid, and others are invalid, the rules only apply to the already valid ones. Thus the user may correct the wrong values in columns but not make in-correct changes to a currently valid column.

Listing F shows the implementation of Goals 3 and 4. At this point, we’ve achieved our first four goals. Not bad for a couple of triggers, two views, and a simple package! Next month, we’ll travel further down our eight-fold path to server-side enlightenment.

Listing F: This trigger enforces goals on updates of existing employee records.

CREATE OR REPLACE TRIGGER emp_bur 
  BEFORE UPDATE ON employee FOR EACH ROW
DECLARE
  install_status_unknown EXCEPTION;
  need_data              EXCEPTION;
  unwanted_data          EXCEPTION;
  no_change_allowed      EXCEPTION;
  ws_is_pr_installed company_control.Payroll_installed_ind%TYPE
     := system_control.is_Payroll_installed(:new.company_id);
  ws_is_jc_installed company_control.jobcost_installed_ind%TYPE
     := system_control.is_jobcost_installed(:new.company_id);
BEGIN
  IF ws_is_pr_installed = 'E' 
  OR ws_is_jc_installed = 'E' THEN
     RAISE install_status_unknown;
  END IF; 
  - There are four more 
  - combinations.
  IF  ws_is_pr_installed = 'Y'
  AND ws_is_jc_installed = 'Y' THEN
     - Citizen is mandatory, unless
     - already blank. 
     - (Incremental update.)
     IF  :new.citizenship IS NULL 
     AND :old.citizenship IS NOT NULL
     THEN
        RAISE need_data;
     END IF;	
  END IF;
  IF  ws_is_pr_installed = 'Y'
  AND ws_is_jc_installed = 'N' THEN
    - Citizen is mandatory, unless
    - already blank. 
    - (Incremental update.)
    IF  :new.citizenship IS NULL
    AND :old.citizenship IS NOT NULL
    THEN
        RAISE need_data;
    END IF;
  END IF;
  IF  ws_is_pr_installed = 'N'
  AND ws_is_jc_installed = 'Y' THEN
      - Don't want annual_salary
      - recorded, but don't change
      - it if already there.
      IF (    :new.annual_salary IS NOT NULL
          AND :old.annual_salary IS NOT NULL
          AND :old.annual_salary != :new.annual_salary
         ) 
      OR (    :new.annual_salary IS NULL
          AND :old.annual_salary IS NOT NULL
         )
      OR (    :new.annual_salary IS NOT NULL
          AND :old.annual_salary IS NULL
         ) THEN
         RAISE no_change_allowed;
      END IF;
      - Make citizenship blank, regardless of old value.
      :new.citizenship := NULL;
  END IF;
  IF  ws_is_pr_installed = 'N'
  AND ws_is_jc_installed = 'N' THEN
      - We don't record employees at all.
      RAISE unwanted_data;
  END IF;
EXCEPTION
  WHEN install_status_unknown THEN
    RAISE_APPLICATION_ERROR(-20001,'Install status unknown.');
  WHEN unwanted_data THEN
    RAISE_APPLICATION_ERROR(-20001,'Unwanted data.');
  WHEN need_data THEN
    RAISE_APPLICATION_ERROR(-20001,'Need more data.');
  WHEN no_change_allowed THEN
    RAISE_APPLICATION_ERROR(-20001,'No change allowed.');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE || ':' || SQLERRM); 
END;
/

[The Cobb Group Home Page]

Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.